#!/usr/bin/env python
# encoding: utf-8
"""
DatabaseController.py

Created by Pablo Ortega Mesa on 2011-01-28.
Copyright (c) 2011 Toeska Research Group. All rights reserved.
"""
from xml.dom import minidom
import MySQLdb
import sys
import re
import getopt
from latticeXMLFunctions import *
###################################################################################
###																				###
###								TABLE LATTICE									###
###																				###
###################################################################################
def insert_TableLattice(xmlFile,name,inv=1):
	lat = {}
	try:
		dom = minidom.parse(xmlFile)
		# db_stat
		element = dom.getElementsByTagName('db_stat')
		if len(element)==1:
			element = element[0]
			for i in xrange(0,len(element.childNodes)):
				if element.childNodes[i].nodeName == "db_filename":
					lat["contextFile"] = (element.childNodes[i].childNodes[0].nodeValue,"text")
				elif element.childNodes[i].nodeName == "nb_lines":
					lat["sizeObj"] = (element.childNodes[i].childNodes[0].nodeValue,"int")
				elif element.childNodes[i].nodeName == "largest_attr":
					lat["sizeAttr"] = (element.childNodes[i].childNodes[0].nodeValue,"int")
				elif element.childNodes[i].nodeName == "nb_attr_avg":
					lat["avgAttr"] = (element.childNodes[i].childNodes[0].nodeValue.replace(",","."),"float")
				elif element.childNodes[i].nodeName == "density":
					lat["density"] = (element.childNodes[i].childNodes[0].nodeValue[:-1].replace(",","."),"float")
				elif element.childNodes[i].nodeName == "min_supp_abs":
					lat["minSupAbs"] = (element.childNodes[i].childNodes[0].nodeValue,"int")
				elif element.childNodes[i].nodeName == "min_supp_rel":
					lat["minSupRel"] = (element.childNodes[i].childNodes[0].nodeValue[:-1].replace(",","."),"float")
				elif element.childNodes[i].nodeName == "alg":
					lat["algorithm"] = (element.childNodes[i].childNodes[0].nodeValue,"text")
		element = dom.getElementsByTagName('lattice_stat')
		if len(element)==1:
			element = element[0]
			for i in xrange(0,len(element.childNodes)):
				if element.childNodes[i].nodeName=="nb_inner_nodes":
					lat["sizeConcept"] = (element.childNodes[i].childNodes[0].nodeValue,"int")
				if element.childNodes[i].nodeName=="top":
					idTop = element.childNodes[i].attributes.get("concept_ref").value
				if element.childNodes[i].nodeName=="bottom":
					idBottom = element.childNodes[i].attributes.get("concept_ref").value
		lat["nameLattice"] = (name,"text")
		lat["latInverted"] = (inv,"int")
		query = "INSERT INTO Lattice("
		for colName in lat:
			query+=colName+","
		query = query[:-1]+") VALUES("
		for colName in lat:
			if lat[colName][1] == "text":
				query += "'"+lat[colName][0]+"',"
			else:
				query += str(lat[colName][0])+","
		query = query[:-1]
		query += ");"
		query2 = "SELECT id FROM Lattice WHERE nameLattice LIKE \'%"+lat['nameLattice'][0]+"%\'ORDER BY id DESC LIMIT 1"
		db=MySQLdb.connect(host='localhost',user='lattice',passwd='lattice',db='Lattices2')
		cursor=db.cursor()
		try:
			cursor.execute(query)
		except:
			print "ERROR: Lattice with name of lattice already exists, please use another name"
			return None,None,None
		db.commit()
		cursor.execute(query2)
		resultado=cursor.fetchone()
		idLattice = resultado[0]
		return idLattice,idTop,idBottom
		
	except:
		print "ERROR: an exception found in parser xml file!"
		return None,None,None

###################################################################################
###																				###
###								TABLE TERMS										###
###																				###
###################################################################################		

def insert_TableTerms(idLattice,xmlFile):
	terms = []
	try:
		dom = minidom.parse(xmlFile)
		elements = dom.getElementsByTagName('attr')
		if len(elements) != 0:
			for i in range(0,len(elements)):
				attrId = elements[i].attributes["id"].value
				attrValue = elements[i].childNodes[0].nodeValue
				t = (attrId,attrValue)
				terms.append(t)
		else:
			print 'Error: This lattice dont contain any attribue'
	except:
		print "ERROR: an exception found in parser xml file!"
		return None
	
	db=MySQLdb.connect(host='localhost',user='lattice',passwd='lattice',db='Lattices2')
	cursor=db.cursor()
	for term in terms:
		query = "INSERT INTO Terms(name,value,idLattice) VALUES(\""+str(term[1])+"\",\""+str(term[1])+"\","+str(idLattice)+");"
		try:
			cursor.execute(query)		
		except:
			print "ERROR: SQL Exception found!"
			return None
	db.commit()	

def select_TableTerms(name,idLattice):
	db=MySQLdb.connect(host='localhost',user='lattice',passwd='lattice',db='Lattices2')
	cursor=db.cursor()
	query="SELECT id FROM Terms WHERE name LIKE \'"+name+"\' AND idLattice="+str(idLattice)+" LIMIT 1"
	cursor.execute(query)
	resultado=cursor.fetchone()
	if len(resultado)>0:
		return resultado[0]
	else:
		return None
		
###################################################################################
###																				###
###								TABLE DOCS										###
###																				###
###################################################################################
		
def insert_TableDocs(idLattice,valuesFile,path):
	docs = []
	vFile = open(valuesFile,"r")
	for vLine in vFile:
		vLine = vLine.split("\n")[0]
		filename = vLine.split("\t")[1]
		filename = re.sub('[^a-zA-Z0-9_. ]','',filename)
		filename = re.sub(' ','_',filename)
		filename = "api_"+filename+".txt"
		doc = (vLine.split("\t")[0],path+filename)
		docs.append(doc)
	db=MySQLdb.connect(host='localhost',user='lattice',passwd='lattice',db='Lattices2')
	cursor=db.cursor()
	for doc in docs:
		query = "INSERT INTO Docs(name,value,idLattice) VALUES(\""+str(doc[0])+"\",\""+str(doc[1])+"\","+str(idLattice)+");"
		try:
			cursor.execute(query)
		except:
			print "ERROR: SQL Exception found!"
			return None
	db.commit()	
def select_TableDocs(name,idLattice):
	db=MySQLdb.connect(host='localhost',user='lattice',passwd='lattice',db='Lattices2')
	cursor=db.cursor()
	query="SELECT id FROM Docs WHERE name LIKE \'"+name+"\' AND idLattice="+str(idLattice)+" LIMIT 1"
	cursor.execute(query)
	resultado=cursor.fetchone()
	if resultado:
		return resultado[0]
	else:
		return None
		
###################################################################################
###																				###
###								TABLE CONCEPT									###
###																				###
###################################################################################		

def insert_TableConcept(xmlFile,idLattice,idTop,idBottom):
	attrs,objs=getAttrObj(xmlFile)
	concepts = []
	try:
		dom = minidom.parse(xmlFile)
		elements = dom.getElementsByTagName('concept')
		if len(elements)!=0:
			db=MySQLdb.connect(host='localhost',user='lattice',passwd='lattice',db='Lattices2')
			cursor=db.cursor()
			for i in xrange(0,len(elements)):
				conceptType = elements[i].attributes["type"].value
				concept = {}
				concept["number"] = (elements[i].attributes["id"].value,"int")
				concept["idLattice"] = (idLattice,"int")
				concept["support"] = (elements[i].attributes["supp_abs"].value,"int")
				concept["level"] = (elements[i].attributes["level"].value,"int")
				concept["stability"] = (elements[i].attributes["stability"].value,"float")
				query = "INSERT INTO Concept("
				
				for colName in concept:
					query+=colName+","
				query = query[:-1]+") VALUES("
				for colName in concept:
					if concept[colName][1] == "text":
						query += "'"+concept[colName][0]+"',"
					else:
						query += str(concept[colName][0])+","
				query = query[:-1]
				query += ");"
				try:
					cursor.execute(query)
				except:
					print "ERROR: SQL Exception found in insert concept number: "+concept["number"]
				
				if (conceptType != "top" and conceptType != "bottom"):	
					#guardar el intent
					for j in xrange(0,len(elements[i].childNodes)):
						if elements[i].childNodes[j].nodeName == "intent":
							intentIds = elements[i].childNodes[j].childNodes[0].nodeValue.split(",")
							intent = []
							for t in intentIds:
								intent.append(attrs[t])
							concept["intent"] = intent
						elif elements[i].childNodes[j].nodeName == "extent":
							extentIds = elements[i].childNodes[j].childNodes[0].nodeValue.split(",")
							extent = []
							for d in extentIds:
								extent.append(objs[d])
							concept["extent"] = extent
						elif elements[i].childNodes[j].nodeName == "parents":
							parents = elements[i].childNodes[j].childNodes[0].nodeValue.split(",")
							concept["parents"] = parents
						elif elements[i].childNodes[j].nodeName == "children":
							children = elements[i].childNodes[j].childNodes[0].nodeValue.split(",")
							concept["children"] = children
					concepts.append(concept)
			db.commit()
			return concepts
							
	except:
		print "ERROR: an exception found in parser xml file!"
		return None
def select_TableConcept(number,idLattice):
	query = "SELECT id FROM Concept WHERE number="+str(number)+" AND idLattice="+str(idLattice)+" LIMIT 1"
	db=MySQLdb.connect(host='localhost',user='lattice',passwd='lattice',db='Lattices2')
	cursor=db.cursor()
	try:
		cursor.execute(query)
	except:
		print "ERROR: SQL Exception found in select concept number: "+str(number)
		return None
	resultado = cursor.fetchone()
	if resultado:
		return resultado[0]
	else:
		return None
###################################################################################
###																				###
###								TABLE EXTENT									###
###																				###
###################################################################################	
def insert_TableExtent(concept,idLattice):
	db=MySQLdb.connect(host='localhost',user='lattice',passwd='lattice',db='Lattices2')
	cursor=db.cursor()
	for doc in concept["extent"]:
		idDoc = select_TableDocs(doc,idLattice)
		idConcept = select_TableConcept(concept["number"][0],idLattice)
		query = "INSERT INTO Extent(idDoc,idConcept) VALUES("+str(idDoc)+","+str(idConcept)+");"
		try:
			cursor.execute(query)
		except:
			print "ERROR: SQL Exception found in query: "+query
			return None
	db.commit()
def select_TableExtent(numberConcept,doc,idLattice):
	idDoc = select_TableDocs(doc,idLattice)
	idConcept = select_TableConcept(numberConcept,idLattice)
	query = "SELECT id FROM Extent WHERE idDoc="+str(idDoc)+" AND idConcept="+str(idConcept)+" LIMIT 1"
	db=MySQLdb.connect(host='localhost',user='lattice',passwd='lattice',db='Lattices2')
	cursor=db.cursor()
	try:
		cursor.execute(query)
	except:
		print "ERROR: SQL Exception found"
		return None
	resultado = cursor.fetchone()
	if resultado:
		return resultado[0]
	else:
		return None
	
###################################################################################
###																				###
###								TABLE INTENT									###
###																				###
###################################################################################
def insert_TableIntent(concept,idLattice):
	db=MySQLdb.connect(host='localhost',user='lattice',passwd='lattice',db='Lattices2')
	cursor=db.cursor()
	for term in concept["intent"]:
		idTerm = select_TableTerms(term,idLattice)
		idConcept = select_TableConcept(concept["number"][0],idLattice)
		query = "INSERT INTO Intent(idTerm,idConcept) VALUES("+str(idTerm)+","+str(idConcept)+");"	
		try:
			cursor.execute(query)
		except:
			print "ERROR: SQL Exception found in query: "+query
			return None
	db.commit()
def select_TableIntent(numberConcept,term,idLattice):
	idTerm = select_TableTerms(term,idLattice)
	idConcept = select_TableConcept(numberConcept,idLattice)
	query = "SELECT id FROM Intent WHERE idTerm="+str(idTerm)+" AND idConcept="+str(idConcept)+" LIMIT 1"
	db=MySQLdb.connect(host='localhost',user='lattice',passwd='lattice',db='Lattices2')
	cursor=db.cursor()
	try:
		cursor.execute(query)
	except:
		print "ERROR: SQL Exception found"
		return None
	resultado = cursor.fetchone()
	if resultado:
		return resultado[0]
	else:
		return None
###################################################################################
###																				###
###								TABLE RELATION									###
###																				###
###################################################################################
def insert_TableRelation(concept,idLattice):
	db=MySQLdb.connect(host='localhost',user='lattice',passwd='lattice',db='Lattices2')
	cursor = db.cursor()
	padres = concept["parents"]
	idHijo = select_TableConcept(concept["number"][0],idLattice)
	if idHijo:
		for padre in padres:
			idPadre = select_TableConcept(padre,idLattice)
			if idPadre:
				queryValidate = "SELECT id FROM Relation WHERE idConcept1="+str(idPadre)+" AND idConcept2="+str(idHijo)+" LIMIT 1"
				cursor.execute(queryValidate)
				resultados = cursor.fetchone()
				if resultados==None:
					query = "INSERT INTO Relation(idConcept1,idConcept2) VALUES("+str(idPadre)+","+str(idHijo)+")"
					cursor.execute(query)
	db.commit()
	hijos = concept["children"]
	idPadre = select_TableConcept(concept["number"][0],idLattice)
	if idPadre:
		for hijo in hijos:
			idHijo = select_TableConcept(hijo,idLattice)
			if idHijo:
				queryValidate = "SELECT id FROM Relation WHERE idConcept1="+str(idPadre)+" AND idConcept2="+str(idHijo)+" LIMIT 1"
				cursor.execute(queryValidate)
				resultados = cursor.fetchone()
				if resultados==None:
					query = "INSERT INTO Relation(idConcept1,idConcept2) VALUES("+str(idPadre)+","+str(idHijo)+")"
					cursor.execute(query)
	db.commit()



options, remainder = getopt.getopt(sys.argv[1:],'x:v:p:n:',['xml=',
														'values=',
														'path=',
														'name='])											


xmlFile = False
valuesFile = False
path = False
name = None			
for opt,arg in options:
	if opt in ('-x','--xml'):
		xmlFile = arg
	if opt in ('-v','--values'):
		valuesFile = arg
	if opt in ('-p','--path'):
		path = arg
	if opt in ('-n','--name'):
		name = arg

if not xmlFile or not valuesFile or not name or not path:
	print "USAGE python "+sys.argv[0]+" --xml latticeFileFormatXML.xml --values ApisIds.txt --path /opt/GenerateDataset/Corporas/v3/apis/ --name name_of_lattice" 
	exit(0)

idLattice,idTop,idBottom = insert_TableLattice(xmlFile,name)
insert_TableTerms(idLattice,xmlFile)
insert_TableDocs(idLattice,valuesFile,path)
concepts = insert_TableConcept(xmlFile,idLattice,idTop,idBottom)
for c in concepts:
	print "Extent of concept: "+c["number"][0]
	insert_TableExtent(c,idLattice)
	print "Intent of concept: "+c["number"][0]
	insert_TableIntent(c,idLattice)
	print "Relation of concept: "+c["number"][0]
	insert_TableRelation(c,idLattice)
